import xlrd
import datetime
import QuantLib as ql
import pandas as pd
import seaborn as sb
import Core.Gadget as Gadget
import Analysis.TermStructure
import matplotlib.pyplot as plt #绘图
import numpy as np

# 部分需要映射简称的银行
def Alias_Mapping():
    mapping = {}
    mapping["杭州联合银行"] = "杭州联合农商行"
    mapping["上海农商行"] = "上海农商银行"
    mapping["南昌银行"] = "江西银行"
    mapping["西安商行"] = "西安银行"
    mapping["民泰商行"] = "浙江民泰商业银行"
    mapping["鄞州银行"] = "宁波鄞州农商行"
    mapping["稠州商行"] = "浙江稠州商业银行"
    mapping["华润银行"] = "珠海华润银行"
    mapping["厦门商行"] = "厦门银行"
    mapping["广东华兴"] = "广东华兴银行"
    mapping["华兴银行"] = "广东华兴银行"
    mapping["滨海银行"] = "天津滨海农商行"
    #
    return mapping

# A 没有信用风险：国有商业银行（国有，准国有机构），
# B 基本没有信用风险：大型，股份制商业银行，商业银行，资本金 > 100亿 （P99）
# C 有一定信用风险：中小型，城市商业银行，农商行，资本金 > 10亿 （P90）
# D 有很大信用风险：小型，城市商业银行，农商行，其他非银行持牌机构
def Bank_Rating(database):
    #
    def Set_BankRating(x):
        if x["Bank_Type"] == "国有商业银行":
            return "A"
        if x["Capital"] > 100:
            return "B"
        if x["Capital"] > 10:
            return "C"
        return "D"

    documents_bank = database.Find("Institution", "Bank")
    df_banks = Gadget.DocumentsToDataFrame(documents_bank, keep=["Key", "Name", "Alias", "Bank_Type", "Capital"])
    df_banks["Rating"] = ""
    df_banks["Rating"] = df_banks.apply(lambda x: Set_BankRating(x), axis=1)
    # print(df_banks)
    return df_banks


# 非银行机构评级
# A 没有信用风险：大型国有，准国有机构，省级国资委控股
# B 基本没有信用风险：大型商业机构，省以下国资委控股，A-AA券商，
# C 有一定信用风险：中小型商业机构
# D 有很大信用风险：小型商业机构，非银行，保险，券商机构，非金融持牌机构
def Mapping_Tempo_Rating():
    mapping = {}
    mapping["和合资管"] = "D" # 和合期货
    mapping["东方点石"] = "D" #
    mapping["先融资管"] = "A" # 国资委直属
    mapping["东兴证券"] = "B" # 券商评级A
    mapping["联储证券"] = "B" #
    mapping["银行加商户"] = "B"
    return mapping

# 读取银行列表（wind导出）
# 有脏数据，已经手动修改，没事不要运行该函数，否则会覆盖现有数据
def Load_BankList(database, pathFilename):
    #
    wb = xlrd.open_workbook(filename=pathFilename)
    sheet = wb.sheet_by_index(0)  # 通过索引获取表格
    #
    nrows = sheet.nrows
    ncols = sheet.ncols
    documents = []
    headerIndexByName = {}
    for i in range(0, nrows):
        row_data = sheet.row_values(i)
        content = row_data
        print(content)
        #
        if i == 0:
            headerCount = 0
            for header in content:
                header = header.strip(" ")
                header = header.strip("\n")
                headerIndexByName[header] = headerCount
                headerCount = headerCount + 1
            continue
        #
        if content[0] == "":
            break
        #
        document = {}
        #
        sdt = row_data[headerIndexByName["成立日期"]]
        try:
            dt = datetime.datetime.strptime(sdt, "%Y%m%d")
            document["Setup_Date"] = dt.date()
        except:
            document["Setup_Date"] = None

        #
        listed = row_data[headerIndexByName["是否上市"]]
        if listed == "是":
            document["Is_Listed"] = True
        else:
            document["Is_Listed"] = False
        #
        document["English_Name"] = row_data[headerIndexByName["英文名称"]]
        document["Name"] = row_data[headerIndexByName["公司名称"]]
        document["Alias"] = row_data[headerIndexByName["公司简称"]]
        document["Symbol"] = row_data[headerIndexByName["上市代码"]]
        document["Capital"] = row_data[headerIndexByName["注册资本(亿元)"]]
        document["Bank_Type"] = row_data[headerIndexByName["银行类型"]]
        document["Province"] = row_data[headerIndexByName["省份"]]
        document["City"] = row_data[headerIndexByName["城市"]]
        document["Register_Address"] = row_data[headerIndexByName["注册地址"]]
        #
        document["Date"] = document["Setup_Date"]
        document["Key2"] = document["Name"]

        for k, v in document.items():
            if v == "":
                document[k] = None
        #
        documents.append(document)
        if len(documents) > 100:
            database.Upsert_Many("Institution", "Bank", [], documents)
            documents.clear()
        #
    database.Upsert_Many("Institution", "Bank", [], documents)


# 读取券商列表（wind导出）
def Load_BrokerageList(database, pathFilename):
    #
    wb = xlrd.open_workbook(filename=pathFilename)
    sheet = wb.sheet_by_index(0)  # 通过索引获取表格
    #
    nrows = sheet.nrows
    ncols = sheet.ncols
    documents = []
    headerIndexByName = {}
    for i in range(0, nrows):
        row_data = sheet.row_values(i)
        content = row_data
        # print(content)
        #
        if i == 0:
            headerCount = 0
            for header in content:
                header = header.strip(" ")
                header = header.strip("\n")
                headerIndexByName[header] = headerCount
                headerCount = headerCount + 1
            continue
        #
        if content[0] == "":
            break
        #
        document = {}
        #
        sdt = row_data[headerIndexByName["成立日期"]]
        try:
            dt = datetime.datetime.strptime(sdt, "%Y%m%d")
            document["Setup_Date"] = dt.date()
        except:
            document["Setup_Date"] = None
        #
        listed = row_data[headerIndexByName["是否上市"]]
        if listed == "是":
            document["Is_Listed"] = True
        else:
            document["Is_Listed"] = False
        #
        document["Name"] = row_data[headerIndexByName["公司名称"]]
        #
        names = document["Name"].split("证券")
        if len(names) > 1:
            document["Alias"] = names[0] + "证券"
        else:
            document["Alias"] = None

        document["English_Name"] = row_data[headerIndexByName["英文名称"]]
        document["Capital"] = row_data[headerIndexByName["注册资本(亿元)"]]
        document["Province"] = row_data[headerIndexByName["省份"]]
        document["City"] = row_data[headerIndexByName["城市"]]
        document["Register_Address"] = row_data[headerIndexByName["注册地址"]]
        #
        document["Date"] = document["Setup_Date"]
        document["Key2"] = document["Name"]

        for k, v in document.items():
            if v == "":
                document[k] = None
        #
        documents.append(document)
        if len(documents) > 100:
            database.Upsert_Many("Institution", "Brokerage", [], documents)
            documents.clear()
    #
    database.Upsert_Many("Institution", "Brokerage", [], documents)


def Check_BankCoverage(database):
    #
    banks = []
    documents_bank = database.Find("Institution", "Bank")
    for document in documents_bank:
        banks.append(document["Alias"])
    #
    bank_mapping = Alias_Mapping()
    nonBank_mapping = Mapping_Tempo_Rating()
    #
    documents = database.Find("FixIncome_Similar", "Licai_yanglee")
    for document in documents:
        name = document["Issuer"]
        name2 = bank_mapping.get(name)
        name3 = "中国" + name
        name4 = nonBank_mapping.get(name)
        #
        if name in banks:
            pass
        elif name2 != None and name2 in banks:
            pass
        elif name3 in banks:
            pass
        elif name4 != None:
            pass
        else:
            print(name)
            a = 0


# ---在理财列表上填充银行评级信息---
# 必须拥有issuer字段
def Fill_BankRating(database, df_licai_list):
    #
    def Func(x, rating_mapping, alias_mapping):
        #
        name = x["Issuer"]
        name2 = alias_mapping.get(name)
        name3 = "中国" + name

        # ---临时评级---
        non_bank_rating = Mapping_Tempo_Rating()
        if non_bank_rating.get(name):
            return non_bank_rating.get(name)

        final_name = ""
        if name in rating_mapping: # issuer 能否直接找到评级
            final_name = name
        elif name2 != None and name2 in rating_mapping: # 临时简称对照表
            final_name = name2
        elif name3 in rating_mapping: # 加中国二字能否找到评级
            final_name = name3
        else:
            print(name, "找不到对应的评级")
            return None

        return rating_mapping.get(final_name)

    #
    df_bank_rating = Bank_Rating(database)
    #
    rating_mapping = {}
    for index, row in df_bank_rating.iterrows():
        alias = row["Alias"]
        name = row["Name"]
        rating = row["Rating"]
        if alias == None:
            continue
        if alias in rating_mapping.keys():
            print(name, alias)
        else:
            rating_mapping[alias] = rating

    #
    alias_mapping = Alias_Mapping()
    df_licai_list["Rating"] = df_licai_list.apply(lambda x: Func(x, rating_mapping, alias_mapping), axis=1)


def Calc_TermPremium(curve, term, reference_date):
    #
    try:
        num_term = int(term)
    except:
        return None
    #
    reference_date2 = ql.Date(reference_date.day, reference_date.month, reference_date.year)
    dc = ql.ActualActual(ql.ActualActual.Actual365)
    future_date = reference_date2 + ql.Period(num_term, ql.Days)
    rate = 100.0 * curve.zeroRate(future_date, dc, ql.Compounded, ql.Annual).rate()
    return rate


# ---基于历史数据，估算信用溢价---
def Calc_CreditPremium_Baseline(database, datetime1, datetime2, save=False):
    #
    def Set_CreditPremium(x, curve, reference_date):
        term = x["Term"]
        try:
            num_term = int(term)
        except:
            print("Can't Parse Term", x["Term"], x["Name"])
            return None
        #
        if num_term > 2 * 365:
            print("Too Long Term", x["Term"], x["Name"])
            return None
        #
        rate = Calc_TermPremium(curve, num_term, reference_date)
        return rate

    # ---读取理财数据---
    filter = {}
    filter["Date"] = {">=": datetime1.date(), "<=": datetime2.date()}
    documents_licai = database.Find("FixIncome_Similar", "Licai_yanglee", filter=filter)
    df_licai = Gadget.DocumentsToDataFrame(documents_licai, keep=["Name", "Issuer", "Date", "Term", "Expected_Return"])
    # print(df_licai)

    Fill_BankRating(database, df_licai)

    # ---Build term structure---
    reference_date = datetime2.date()
    curve = Analysis.TermStructure.Build_TBond_TermStructure_Curve(database, datetime1, datetime2)
    # terms = [0, 3, 6, 9, 12, 24]
    # rates = [1.4197, 2.0373, 2.1576, 2.3061, 2.4264, 2.5006]
    # curve = Analysis.TermStructure.Build_TermStructure_Curve(reference_date, terms, rates, plot=False)

    # ---计算每个产品，在不同期限的 Credit Premium
    df_licai["Credit_Premium"] = df_licai.apply(lambda x: Set_CreditPremium(x, curve, reference_date), axis=1)
    print(df_licai.head())

    #
    df_groups = df_licai.groupby(["Rating"])
    for df_group in df_groups:
        rating = df_group[0]
        df = df_group[1].copy()
        size = len(df)
        credit_premium = df["Credit_Premium"].mean()
        print(rating, credit_premium, "#", size)
        #
        if save:
            document = {"Date": reference_date, "Rating": rating, "Credit_Premium": credit_premium,
                        "Sample_Size": size,
                        "DateTime1": datetime1.date(), "DateTime2": datetime2.date()}
            document["Key2"] = document["Rating"] + "_" + Gadget.ToDateString(reference_date)
            database.Upsert("FixIncome_Similar", "Credit_Premium", {}, document)


# ---基于历史数据，估算信用溢价，批量，用于冷启动---
def Calc_CreditPremium_Batch(database):
    # 数据起始于2015年
    datetime1 = datetime.datetime(2015,1,1)
    datetime2 = datetime.datetime.now()

    # 逐月计算即可
    dates = Gadget.GenerateTimeRange_Monthly(datetime1, datetime2)
    for d in dates:
        print(d[0], d[1])
        Calc_CreditPremium_Baseline(database, d[0], d[1], save=True)
        a = 0


def Calc_CreditPremium(credit_premium, rating):
    if credit_premium == None:
        credit_premium = {"A": 2.40, "B": 2.42, "C": 2.44, "D": 2.46}
    return credit_premium.get(rating)


# 计算理财预期收益率
# curve: 基准曲线（国债）
# credit_premium: ABCD 四个等级的信用溢价
# term: 期限
# rating: 银行评级 ABCD
def Calc_Licai_ExpectedReturn(curve, credit_premium, term, rating, datetime2):
    term_premium = Calc_TermPremium(curve, term, datetime2)
    credit_premium = Calc_CreditPremium(credit_premium, rating)
    return term_premium + credit_premium


def Plot_Licai_Picture(datetime1, datetime2, df_licai, title):
    # ---Plot Picture---
    # 只选定1年内曲线
    data = Analysis.TermStructure.Find_TBond_TermStructure_Rate(database, datetime1, datetime2)
    terms = []
    rates = []
    for i in range(len(data["Terms"])):
        term = data["Terms"][i]
        rate = data["Rates"][i]
        if term > 12:
            break
        terms.append(term * 30)
        rates.append(rate)

    # plt.figure(figsize=(8, 4))
    plt.plot(terms, rates, color="blue", linewidth=2, linestyle=':', label='TBond Curve', marker='o')
    # 只plot一年内产品
    df_licai = df_licai[df_licai["Term"] <= 365]
    df_A = df_licai[df_licai["Rating"] == "A"]
    df_B = df_licai[df_licai["Rating"] == "B"]
    df_C = df_licai[df_licai["Rating"] == "C"]
    df_D = df_licai[df_licai["Rating"] == "D"]
    #
    plt.scatter(df_A["Term"], df_A["Expected_Return"], c='red', marker='o', label='Rating A')
    plt.scatter(df_B["Term"], df_B["Expected_Return"], c='g', marker='1', label='Rating B')
    plt.scatter(df_C["Term"], df_C["Expected_Return"], c='orange', marker='2', label='Rating C')
    plt.scatter(df_D["Term"], df_D["Expected_Return"], c='black', marker='x', label='Rating D')
    #
    top = 5
    if len(df_licai) < 5:
        top = len(df_licai)

    for i in range(top):
        row = df_licai.iloc[i]
        name = row["Name"]
        x = row["Term"]
        y = row["Expected_Return"]
        plt.annotate(s=name, xy=(x, y))

    # 叉号：有效前沿
    # plt.scatter(target_variance, target_returns, c=target_returns / target_variance, marker='x')

    # 红星：标记最高sharpe组合
    # plt.plot(maxSharp_profile[1], maxSharp_profile[0], 'r*', markersize=15.0)

    # 黄星：标记最小方差组合
    # plt.plot(minVariance_profile[1], minVariance_profile[0], 'y*', markersize=15.0)
    #
    plt.rcParams['font.sans-serif'] = ['KaiTi']  # 指定默认字体
    plt.rcParams['axes.unicode_minus'] = False  # 解决保存图像是负号'-'显示为方块的问题
    #
    plt.xlim((0, 370))
    my_x_ticks = np.arange(0, 370, 30)
    plt.xticks(my_x_ticks)
    plt.grid(True)
    plt.legend(loc=4)  # 图例展示位置，数字代表第几象限
    plt.title(title + " 期限vs收益率 " + Gadget.ToDateString(datetime1) + " to " + Gadget.ToDateString(datetime2))
    plt.xlabel('Term: Days')
    plt.ylabel('Expected Return')
    # plt.colorbar(label='Sharpe ratio')
    plt.savefig("D://data//licai//licai_" + title + "_performances.png")
    plt.show()


def DataFrameFunc_Calc_Licai_ExpectedReturn(row, database, tbond_curve, datetime2):
    term = row["Term"]
    rating = row["Rating"]
    product_return = float(row["Expected_Return"])
    name = row["Name"]
    #
    try:
        num_term = int(term)
    except:
        print("Can't Parse Term", term, name)
        return None
    #
    if num_term > 2 * 365:
        print("Too Long Term", term, name)
        return None
    #
    expected_return = Calc_Licai_ExpectedReturn(database, tbond_curve, term, rating, datetime2)
    # print(name, product_return, expected_return)
    # a = product_return - expected_return
    return product_return - expected_return


# 评估全市场理财产品（基于爬取数据）
def Analysis_Licai_TotalMarket(database, datetime1, datetime2):
    # ---读取理财数据---
    filter = {}
    filter["Date"] = {">=": datetime1.date(), "<=": datetime2.date()}
    documents_licai = database.Find("FixIncome_Similar", "Licai_yanglee", filter=filter)
    df_licai = Gadget.DocumentsToDataFrame(documents_licai, keep=["Name", "Issuer", "Date", "Term", "Expected_Return"])

    # ---填充银行评级---
    Fill_BankRating(database, df_licai)

    # ---建立国债收益率曲线---
    tbond_curve = Analysis.TermStructure.Build_TBond_TermStructure_Curve(database, datetime1, datetime2)

    # ---建立信用溢价矩阵---
    credit_premium = {"A": 2.60, "B": 2.62, "C": 2.64, "D": 2.66}

    # ---逐个评估产品，根据term，rating，计算预期收益率和Alpha---
    df_licai["Alpha"] = df_licai.apply(lambda x: DataFrameFunc_Calc_Licai_ExpectedReturn(x, tbond_curve, credit_premium, datetime2), axis=1)
    df_licai.sort_values(by="Alpha", ascending=False, inplace=True)

    # ---Export---
    df_licai.to_csv("D://Data//Licai//Licai_TotalMarket_"
                    + Gadget.ToDateString(datetime1) + "_" + Gadget.ToDateString(datetime2) + ".csv",
                    encoding='utf_8_sig')

    # ---Plot Picture---
    Plot_Licai_Picture(datetime1, datetime2, df_licai, title="理财-全市场")


# 评估大麦理财产品(我司在售)
def Analysis_Licai_Damai(database, database_damai, datetime1, datetime2):
    # ---读取理财数据---
    filter = {}
    # filter["Date"] = {">=": datetime1.date(), "<=": datetime2.date()}
    filter["term"] = {">": 0, "<": 730}
    filter["benefit"] = {">": 0}
    # filter["on_sale"] = 1
    filter["biz_name"] = "银行加业务"
    list1 = database_damai.Find("sfmra", "ra_damai_product_item", filter=filter)
    filter["biz_name"] = "银行理财"
    list2 = database_damai.Find("sfmra", "ra_damai_product_item", filter=filter)
    documents_licai = list1 + list2

    #
    df_licai = Gadget.DocumentsToDataFrame(documents_licai,
                                           keep=["sku_name", "merchant_name", "damai_id", "biz_name", "product_type_name", "term", "benefit", "on_sale"],
                                           rename={"sku_name": "Name", "merchant_name": "Issuer", "term": "Term", "benefit": "Expected_Return"})
    # df_licai.to_csv("D://Data//Licai//Licai_Damai_Test.csv", encoding='utf_8_sig')

    # ---去除脏数据， 预处理---
    df_licai = df_licai[df_licai["Expected_Return"] < 1]
    df_licai["Expected_Return"] = df_licai["Expected_Return"] * 100
    df_licai["Term"] = pd.to_numeric(df_licai["Term"])
    df_licai["Expected_Return"] = pd.to_numeric(df_licai["Expected_Return"])

    # ---填充银行评级---
    # ---必须有issuer字段---
    Fill_BankRating(database, df_licai)

    # ---建立国债收益率曲线---
    tbond_curve = Analysis.TermStructure.Build_TBond_TermStructure_Curve(database, datetime1, datetime2)

    # ---建立信用溢价矩阵---
    credit_premium = {"A": 2.60, "B": 2.62, "C": 2.64, "D": 2.66}

    # ---逐个评估产品，根据term，rating，计算预期收益率和Alpha---
    df_licai["Alpha"] = df_licai.apply(
        lambda x: DataFrameFunc_Calc_Licai_ExpectedReturn(x, tbond_curve, credit_premium, datetime2), axis=1)
    df_licai.sort_values(by="Alpha", ascending=False, inplace=True)

    # ---Export---
    df_licai.to_csv("D://Data//Licai//Licai_Damai_"
                    + Gadget.ToDateString(datetime1) + "_" + Gadget.ToDateString(datetime2) + ".csv",
                    encoding='utf_8_sig')

    # ---只保留在售---
    df_licai = df_licai[df_licai["on_sale"] == "1"]

    # ---Plot Picture---
    Plot_Licai_Picture(datetime1, datetime2, df_licai, title="理财-大麦在售")


if __name__ == '__main__':
    #
    from Core.Config import *
    cfgPathFilename = os.getcwd() + "/../config.json"
    config = Config(cfgPathFilename)
    database = config.DataBase("MySQL")
    realtime = config.RealTime()

    # ---读取与检测---
    # Load_BankList(database, "d://data//misc//商业银行基本资料.xlsx")
    # Load_BrokerageList(database, "d://data//misc//券商基本资料.xlsx")
    # Check_BankCoverage(database)
    # Bank_Rating(database)
    # Fill_BankRating(database, None)

    datetime1 = datetime.datetime(2019, 1, 1)
    datetime2 = datetime.datetime(2019, 12, 31)
    # Calc_CreditPremium_Baseline(database, datetime1, datetime2)
    # Calc_CreditPremium_Batch(database)

    # ---全市场评估---
    datetime1 = datetime.datetime(2019, 12, 1)
    datetime2 = datetime.datetime(2019, 12, 31)
    # Analysis_Licai_TotalMarket(database, datetime1, datetime2)

    # ---Damai 产品评估---
    datetime1 = datetime.datetime(2019, 12, 1)
    datetime2 = datetime.datetime(2019, 12, 31)
    database_damai = MySQLDB.MySQLDB(address="172.25.4.213",
                                     port="3306",
                                     username="m_sfmra",
                                     password="")
    #
    Analysis_Licai_Damai(database, database_damai, datetime1, datetime2)
